﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DrSoftsFrame.Servers;
using System.Data.SqlClient;
using PreeRegisterModel;

namespace PreeRegisterDAL
{
    public class SearchDAL
    {
        /// <summary>
        /// 查询待交费  
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public static DataSet NotPay(NotPayRequestModel notPayRequestModel)
        {
            ClinicDb mydb = new ClinicDb();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_GetBillInfo";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@indextype", SqlDbType.Int);
                p1.Direction = ParameterDirection.Input;
                p1.Value = notPayRequestModel.indextype;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@patientcode", SqlDbType.VarChar,30);
                p2.Direction = ParameterDirection.Input;
                p2.Value = notPayRequestModel.cpatientcode;
                cmd.Parameters.Add(p2);

                SqlParameter p3 = new SqlParameter("@oid", SqlDbType.VarChar, 50);
                p3.Direction = ParameterDirection.Input;
                p3.Value = notPayRequestModel.orderid;
                cmd.Parameters.Add(p3);

                SqlParameter p4 = new SqlParameter("@CateID", SqlDbType.Int);
                p4.Direction = ParameterDirection.Input;
                p4.Value = notPayRequestModel.cateid;
                cmd.Parameters.Add(p4);

                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1)
                {
                    return null;
                }
                return ds;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 查询就诊状态  
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public static DataSet DiagnoseState(DiagnoseStateRequestModel diagnoseStateRequestModel)
        {
            MZOpDb mydb = new MZOpDb();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_HisCis_PatientState";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@idiagnoseid", SqlDbType.Int);
                p1.Direction = ParameterDirection.Input;
                p1.Value = diagnoseStateRequestModel.diagnoseid;
                cmd.Parameters.Add(p1);

                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1||ds.Tables[0].Rows.Count<1)
                {
                    return null;
                }
                return ds;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 查询排队叫号
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public static DataSet ClinicQueue(ClinicQueueRequestModel clinicQueueRequestModel)
        {
            MZOpDb mydb = new MZOpDb();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_HisCis_CurrentDoctorInfo";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@idiagnoseid", SqlDbType.Int);
                p1.Direction = ParameterDirection.Input;
                p1.Value = clinicQueueRequestModel.diagnoseid;
                cmd.Parameters.Add(p1);

                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 2 || ds.Tables[0].Rows.Count < 1)
                {
                    return null;
                }
                return ds;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 查询门诊已缴费
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public static DataSet ClinicFare(ClinicFareRequestModel clinicFareRequestModel)
        {
            ClinicDb mydb = new ClinicDb();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_rate_query";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@cpatientcoce", SqlDbType.VarChar,20);
                p1.Direction = ParameterDirection.Input;
                p1.Value = clinicFareRequestModel.cpatientcode;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@start_date", SqlDbType.Int);
                p2.Direction = ParameterDirection.Input;
                p2.Value = clinicFareRequestModel.startdate;
                cmd.Parameters.Add(p2);

                SqlParameter p3 = new SqlParameter("@end_date", SqlDbType.Int);
                p3.Direction = ParameterDirection.Input;
                p3.Value = clinicFareRequestModel.enddate;
                cmd.Parameters.Add(p3);

                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1)
                {
                    return null;
                }
                return ds;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 住院费用查询
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public static DataSet HospitalFare(HospitalFareRequestModel hospitalFareRequestModel)
        {
            ClinicDb mydb = new ClinicDb();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "Up_His_InterFace_FareItem";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@InPcode", SqlDbType.VarChar, 20);
                p1.Direction = ParameterDirection.Input;
                p1.Value = hospitalFareRequestModel.cpatientcode;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@IdentityNo_input", SqlDbType.VarChar, 20);
                p2.Direction = ParameterDirection.Input;
                p2.Value = hospitalFareRequestModel.IdentityNo;
                cmd.Parameters.Add(p2);

                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 2||ds.Tables[0].Rows.Count<1)
                {
                    return null;
                }
                return ds;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 人工窗口退费查询  liujia151121
        /// </summary>
        /// <param name="windowFadeFareRequestModel"></param>
        /// <returns></returns>
        public static DataSet WindowFadeFare(WindowFadeFareRequestModel windowFadeFareRequestModel)
        {
            SqlCommand cmd = new SqlCommand();
            string strSql = "";

            //增加是否传入流水号判断：有流水号返回流水号对应窗口退号费记录，没有流水号返回所有未获取的窗口退费记录  liujia151208
            if (windowFadeFareRequestModel.cFlowCode.Trim() == "")
            {
                strSql = string.Format(@"select * from interpay_WindowFadeFare
                                    where isnull(bGetFlag,0)=0");
            }
            else
            {
                strSql = string.Format(@"select * from interpay_WindowFadeFare
                                    where cFlowCode='{0}'", windowFadeFareRequestModel.cFlowCode);
            }

            ClinicDb mydb = new ClinicDb();
            DataSet ds;
            ds = mydb.ExecuteSelect(strSql);
            if (ds == null || ds.Tables.Count < 1)
            {
                return null;
            }
            return ds;
        }

        /// <summary>
        /// 门诊费用发票号 liujia151122
        /// </summary>
        /// <param name="windowFadeFareRequestModel"></param>
        /// <returns></returns>
        public static DataSet ClinicFareBillNum(ClinicFareBillNumRequestModel clinicFareBillNumRequestModel)
        {
            SqlCommand cmd = new SqlCommand();
            string strSql = string.Format(@"select * from opdratemain
                                            where cFlowCode='{0}'", clinicFareBillNumRequestModel.cFlowCode);

            ClinicDb mydb = new ClinicDb();
            DataSet ds;
            ds = mydb.ExecuteSelect(strSql);
            if (ds == null || ds.Tables.Count < 1)
            {
                return null;
            }
            return ds;
        }

        /// <summary>
        /// 置人工窗口退费流水已获取标志 liujia151208
        /// </summary>
        /// <param name="windowFadeFareRequestModel"></param>
        /// <returns></returns>
        public static bool SetWFFFlag(SetWFFFlagRequestModel setWFFFlagRequestModel)
        {
            string strSql = string.Format(@"update interpay_WindowFadeFare
                                            set bGetFlag=1
                                            where cFlowCode='{0}'", setWFFFlagRequestModel.cFlowCode);

            ClinicDb mydb = new ClinicDb();
            int iRow = 0;
            iRow = mydb.ExecuteSqlCmd(strSql);
            if (iRow == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        ///  查看患者是否挂号
        /// </summary>
        /// <param name="diagnoseStateRequestModel"></param>
        /// <returns></returns>
        public static bool getregisterflag(DiagnoseStateRequestModel diagnoseStateRequestModel)
        {
            string sql = string.Format(@"SELECT ISNULL(bImportFlag,0) FROM dbo.HMPreRegister WHERE cPatientCode='{0}' AND orderCode='{1}'", diagnoseStateRequestModel.cpatientcode.ToString(), diagnoseStateRequestModel.ordercode.ToString());
            ClinicDb mydb = new ClinicDb();
            DataSet ds = mydb.ExecuteSelect(sql);
            return DrSoftsFrame.Common.ToBoolean(ds.Tables[0].Rows[0][0]);
        }

        /// <summary>
        ///  查询就诊状态（同煤版）
        /// </summary>
        /// <param name="diagnoseStateRequestModel"></param>
        /// <returns></returns>
        public static DataSet DiagnoseState_tmkyy(DiagnoseStateRequestModel diagnoseStateRequestModel)
        {
            string sql = string.Format(@"SELECT iRegisterID FROM dbo.HMPreRegister WHERE cPatientCode='{0}' AND orderCode='{1}'", diagnoseStateRequestModel.cpatientcode.ToString(), diagnoseStateRequestModel.ordercode.ToString());
            ClinicDb mydb = new ClinicDb();
            DataSet ds = mydb.ExecuteSelect(sql);
            if (ds.Tables[0].Rows.Count < 1)
                return null;

            MZOpDb mzdb = new MZOpDb();
            string mzsql = string.Format(@"select diagnoseflag=  
case   
when isnull(bDiagnoseFlag,0)=0 then 0  
when isnull(bDiagnoseFlag,0)=1 and isnull(bCallFlag,0)<>-1 then 1  
when isnull(bCallFlag,0)=-1 then 2  
end  
from HRDiagnose  
where iRegisterID = {0}  
UNION ALL  
select diagnoseflag=  
case   
when isnull(bDiagnoseFlag,0)=0 then 0  
when isnull(bDiagnoseFlag,0)=1 and isnull(bCallFlag,0)<>-1 then 1  
when isnull(bCallFlag,0)=-1 then 2  
end  
from HRDiagnose_bak  
where iRegisterID = {0}  ", ds.Tables[0].Rows[0][0]);
            DataSet dsmz = mzdb.ExecuteSelect(mzsql);
            return dsmz;


        }

        /// <summary>
        /// 查询挂号记录_根据诊疗ID号与排班ID  liujia151226
        /// </summary>
        /// <param name="windowFadeFareRequestModel"></param>
        /// <returns></returns>
        public static DataSet GetRegisterRecord_ByPatientCodeAndWid(RegisterRecordRequestModel registerRecordRequestModel,string wid)
        {
            string strSql = "";

            strSql = string.Format(@"select b.DeptName,c.StaffName,d.dstarttime,e.cTimeTypeName,a.nDiagnoseNum,a.cFlowCode,a.iDiagnoseCode,a.cPatientCode,a.cPatientName 
from opdregister a
join DictDept b on a.iDeptID=b.no_dept
join dictstaff c on a.iDoctorID=c.no_staff
join opdworkplan d on a.iWorkPlanID=d.iWorkPlanID
join OPDTimeSectType e on d.cTimeTypeCode=e.cTimeTypeCode
where a.cPatientCode='{0}' and a.iWorkPlanID={1}
and a.bFadeRateFlag=0", registerRecordRequestModel.cpatientcode,wid);

            ClinicDb mydb = new ClinicDb();
            DataSet ds;
            ds = mydb.ExecuteSelect(strSql);
            if (ds == null || ds.Tables.Count < 1)
            {
                return null;
            }
            return ds;
        }

        /// <summary>
        /// 查询挂号记录_根据预约订单号  liujia151226
        /// </summary>
        /// <param name="windowFadeFareRequestModel"></param>
        /// <returns></returns>
        public static DataSet GetRegisterRecord_ByOrderid(RegisterRecordRequestModel registerRecordRequestModel)
        {
            string strSql = "";

            strSql = string.Format(@"select b.DeptName,c.StaffName,d.dstarttime,e.cTimeTypeName,a.nDiagnoseNum,a.cFlowCode,a.iDiagnoseCode,a.cPatientCode,a.cPatientName 
from opdregister a
join DictDept b on a.iDeptID=b.no_dept
join dictstaff c on a.iDoctorID=c.no_staff
join opdworkplan d on a.iWorkPlanID=d.iWorkPlanID
join OPDTimeSectType e on d.cTimeTypeCode=e.cTimeTypeCode
where a.iRegisterID in 
(
	select iRegisterID from opdregisterpree
	where ordercode='{0}'
)
and a.bFadeRateFlag=0", registerRecordRequestModel.orderid);

            ClinicDb mydb = new ClinicDb();
            DataSet ds;
            ds = mydb.ExecuteSelect(strSql);
            if (ds == null || ds.Tables.Count < 1)
            {
                return null;
            }
            return ds;
        }

    }
}
